package matching;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
public class mysqlmethods {
	Connection con;
	String dbName;
	String connectS;
	String usernameS;
	String passwordS;
	SimpleDateFormat SQL_DATE_FORMAT;
	
	public mysqlmethods(String c, String u, String p, String db)
	{
		
		this.connect(c, u, p, db);
		
		dbName = db;
		connectS = c;
		usernameS = u;
		passwordS = p;
		
		
	}
	
	public void connect(String c, String u, String p, String tn)//test connection to mysql database
	{
		con = null;
		try {
		    if(tn == "")
		    {
		    	Class.forName("com.mysql.jdbc.Driver").newInstance();
		    	con = DriverManager.getConnection(c,
		        u, p);
		    	
		    	if(!con.isClosed())
			        System.out.println("Successfully connected to " +
			          "MySQL server using TCP/IP...");
		    	
		    	con.close();
		    }
		    else
		    {
		    	Class.forName("com.mysql.jdbc.Driver").newInstance();
		    	con = DriverManager.getConnection(c +"/"+tn,
		        u, p);
		    	
		    	if(!con.isClosed())
			        System.out.println("Successfully connected to MySQL " + tn + ".");
		    }
		      
		  
		  }
		  catch(Exception e) {
			  System.err.println("In Connect");
		      System.err.println("Exception: " + e.getMessage());
		  }
	}
	public void createTable(String n, ArrayList<String> c)
	{
		Iterator<String> iterator = c.iterator();
		String col = "(";
		
		while(iterator.hasNext())
		{
			col += iterator.next() ;
			if(iterator.hasNext())
				col+= ", ";
		}
		col += ");";
		
		System.out.println(col);
		
		while(iterator.hasNext())
			System.out.println(iterator.next().toString());
		Statement st;
		try {
			st = con.createStatement();
			st.executeUpdate("CREATE TABLE IF NOT EXISTS " + n + col);
		      System.out.println("Creating the Table if it doesn't exist");
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
			System.out.println("Issue creating table");
		}
	}
	public ArrayList<String> getAllTables()
	{
		ArrayList<String> a = new ArrayList<String>();
		DatabaseMetaData dbm;
		try {
			dbm = con.getMetaData();
			String[] types = { "TABLE" };
		    ResultSet rs = dbm.getTables(null, null, "%", types);
		    while (rs.next()) {
		      String table = rs.getString("TABLE_NAME");
		      a.add(table);
		      }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			 System.err.println("Exception: " + e.getMessage());
			 System.out.println("Issue getting table names");
		}
		return a;
	    
	}
	public void createDatabase(String n) //will check if it exists or not and if it does not create it
	{
		dbName = n;
		Statement st;
		this.connect(connectS, usernameS, passwordS, n);
		try {
			st = con.createStatement();
			st.executeUpdate("CREATE DATABASE IF NOT EXISTS " + n + ";");
		      System.out.println("Creating the database if it doesn't exist");
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
			System.out.println("Issue creating table");
		}
	      
	}
	private void executeQuery(String s)//Does a SQL Query
	{
		Statement st = null;
		try {
			st = con.createStatement();
			st.executeUpdate(s);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("Exception: " + e.getMessage());
			System.out.println("Issue executing statement");
		}
		
		
	}
	public void connectDatabase(String n)
	{
		dbName = n;
		this.connect(connectS, usernameS, passwordS, n);
		try {
			System.out.println("Attempting to connect to database: " + n);
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
			System.out.println("Issue connecting to database");
		}
		
	}//connect
	public ArrayList<String> getColumnNames(String t)
	{
		ArrayList<String> a = new ArrayList<String>(); 
		ResultSet rsColumns = null;
		 DatabaseMetaData meta;
		try {
			meta = con.getMetaData();
			rsColumns = meta.getColumns(null, null, t, null);
			 while (rsColumns.next()) {
			      a.add(rsColumns.getString("TYPE_NAME"));
			      a.add(rsColumns.getString("COLUMN_NAME"));
			    }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			 System.err.println("Exception: " + e.getMessage());
			 System.out.println("Issue getting column names");
		}
	   return a;
	}
	public void closeConnection()
	{
		try {
			con.close();
			System.out.println("Closing connection");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			 System.err.println("Exception: " + e.getMessage());
			 System.out.println("Issue closing connection");
		}
	}//close connection
	public ArrayList<String> getAllDataFromTable(String t)
	{
		ArrayList<String> a = new ArrayList<String>();
		Statement stmt;
		ResultSet rs;
	
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT * FROM " + t);
			int i = 1;
			int count = 0;
			count = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				for(int j = 1; j <= count; j++ )
				{
					a.add(rs.getString(j));
					i++;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("Exception: " + e.getMessage());
			System.out.println("Issue downloading data");
		}
		return a;
	}//getDataFromTable
	public ArrayList<String> readFromText(String f)
	{
		ArrayList<String> a = new ArrayList<String>();
		
		FileInputStream in;
		try {
			in = new FileInputStream(f);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			String line = null;
			while((line = br.readLine()) != null){
				a.add(line);
				}
								
				in.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return a;
	}
	public String getValue(String id, String column, String t)
	{
		String r = null;
		Statement stmt;
		ResultSet rs;
	
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT " + column +" FROM " + t + " WHERE id = " +id);
			rs.next();//This needs to be called in order to not be Before start of result set
			r = rs.getString(column);
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("Exception: " + e.getMessage());
			System.out.println("Issue getting data");
		}//catch
		return r;
	}
	public int getNextId(String t)
	{
		Statement stmt;
		ResultSet rs;
		int i = -1;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT MAX(id) FROM " + t);
			rs.next();//This needs to be called in order to not be Before start of result set
			i = rs.getInt(1);
			i++;
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("Exception: " + e.getMessage());
			System.out.println("Issue getting data");
		}//catch
		return i;
	}//method
	public void addOffer(String tablename, String togive, String totake, String howlong, String isOffer)
	{
		java.sql.Timestamp  sqlDate = new java.sql.Timestamp(new java.util.Date().getTime());
		
		this.executeQuery("INSERT INTO " + tablename + "(id,togive,totake,howlong,isOffer,created_at,updated_at)" + " VALUES" + "( NULL ,'" + togive + "','" + totake + "','" + howlong + "','" + 
				isOffer + "','" + sqlDate + "','" + sqlDate + "')");
		//NULL VALUE ABOVE IF FOR THE AUTO INCREMENT OF ID
	}
	public void removeOffer(String table, int id)
	{
		this.executeQuery("DELETE FROM " + table + " WHERE ID=" + id);
	}

}
